﻿using NPOI.HSSF.UserModel;
using NPOI.SS.Formula.Eval;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Star.Tools.Excel.Extensions
{
    public static class CellExtensions
    {
        #region
        /// <summary>
        /// 设置列宽
        /// </summary>
        /// <param name="cell"></param>
        /// <param name="width">Excel列宽</param>
        public static void SetColumnWidth(this ICell cell, int width)
        {
            cell.Sheet.SetColumnWidth(cell.ColumnIndex, (int)((width + 0.65) * 256));
        }

        /// <summary>
        /// 设置行高
        /// </summary>
        /// <param name="cell"></param>
        /// <param name="height">一个单位为一个点长度</param>
        public static void SetRowHeight(this ICell cell, short height)
        {
            cell.Row.Height = (short)(height * 20);
        }
        #region 获取Excel列名

        /// <summary>
        /// 获取Excel列名
        /// </summary>
        /// <param name="columnNumber">列的序号，如：A、B、C、AA、BB</param>
        /// <returns></returns>
        static string GetExcelColumnName(int columnNumber)
        {
            int dividend = columnNumber;
            string columnName = String.Empty;
            int modulo;
            while (dividend > 0)
            {
                modulo = (dividend - 1) % 26;
                columnName = Convert.ToChar(65 + modulo).ToString() + columnName;
                dividend = (int)((dividend - modulo) / 26);
            }
            return columnName;
        }
        #endregion 获取Excel列名

        #endregion

        #region 合并单元格
        /// <summary>
        /// 合并单元格
        /// </summary>
        /// <param name="sheet">当前shhet页</param>
        /// <param name="firstRow">起始行</param>
        /// <param name="lastRow">结束行</param>
        /// <param name="firstCol">起始列</param>
        /// <param name="lastCol">结束列</param>
        public static void AddMergedRegion(ISheet sheet, int firstRow, int lastRow, int firstCol, int lastCol)
        {
            sheet.AddMergedRegion(new CellRangeAddress(firstRow, lastRow, firstCol, lastCol));
        }

        /// <summary>
        ///     sheet中添加合并区域
        /// </summary>
        /// <param name="sheet"></param>
        /// <param name="regionInfo"></param>
        public static void AddMergedRegion(ISheet sheet, MergedRegionInfo regionInfo)
        {
            CellRangeAddress region = new CellRangeAddress(regionInfo.FirstRow, regionInfo.LastRow, regionInfo.FirstCol,
                regionInfo.LastCol);
            sheet.AddMergedRegion(region);
        }


        #endregion


        #region 获取单元格的值
        /// <summary>获取单元格的值
        /// </summary>
        /// <param name="cell"></param>
        /// <returns></returns>
        public static object GetCellValue(this ICell cell)
        {
            switch (cell.CellType)
            {

                case CellType.String:
                    string str = cell.StringCellValue;
                    if (str != null && str.Length > 0)
                    {
                        return str.ToString();
                    }
                    else
                    {
                        return null;
                    }
                case CellType.Numeric:
                    if (DateUtil.IsCellDateFormatted(cell))
                    {
                        return DateTime.FromOADate(cell.NumericCellValue).ToString("yyyy-MM-dd HH:mm:ss.fff");
                    }
                    else
                    {
                        return Convert.ToDouble(cell.NumericCellValue);
                    }
                case CellType.Boolean:
                    return cell.BooleanCellValue;
                case CellType.Error:
                    return ErrorEval.GetText(cell.ErrorCellValue);
                case CellType.Blank:
                    return string.Empty;
                case CellType.Formula:
                    switch (cell.CachedFormulaResultType)
                    {
                        case CellType.String:
                            string strFORMULA = cell.StringCellValue;
                            if (strFORMULA != null && strFORMULA.Length > 0)
                            {
                                return strFORMULA.ToString();
                            }
                            else
                            {
                                return null;
                            }
                        case CellType.Numeric:
                            return Convert.ToString(cell.NumericCellValue);
                        case CellType.Boolean:
                            return Convert.ToString(cell.BooleanCellValue);
                        case CellType.Error:
                            return ErrorEval.GetText(cell.ErrorCellValue);
                        //case CellType.Error:
                        //    return cell.ErrorCellValue.ToString(CultureInfo.InvariantCulture);
                        default:
                            return "";
                    }
                default:
                    return "";
            }
        }


        #endregion








        #region 获取单元格CellSpan信息

        /// <summary>
        ///     获取cell的CellSpan信息
        /// </summary>
        /// <param name="cell">单元格</param>
        /// <returns></returns>
        public static CellSpan GetSpan(ICell cell)
        {
            var cellSpan = new CellSpan(1, 1);
            if (cell.IsMergedCell)
            {
                int regionsNum = cell.Sheet.NumMergedRegions;
                for (int i = 0; i < regionsNum; i++)
                {
                    CellRangeAddress range = cell.Sheet.GetMergedRegion(i);
                    if (range.FirstRow == cell.RowIndex && range.FirstColumn == cell.ColumnIndex)
                    {
                        cellSpan.RowSpan = range.LastRow - range.FirstRow + 1;
                        cellSpan.ColSpan = range.LastColumn - range.FirstColumn + 1;
                        break;
                    }
                }
            }
            return cellSpan;
        }

        #endregion





        #region  判断区域内部或交叉

        /// <summary>
        ///     判断是内部或交叉
        /// </summary>
        /// <param name="rangeMinRow"></param>
        /// <param name="rangeMaxRow"></param>
        /// <param name="rangeMinCol"></param>
        /// <param name="rangeMaxCol"></param>
        /// <param name="pictureMinRow"></param>
        /// <param name="pictureMaxRow"></param>
        /// <param name="pictureMinCol"></param>
        /// <param name="pictureMaxCol"></param>
        /// <param name="onlyInternal"></param>
        /// <returns></returns>
        public static bool IsInternalOrIntersect(int? rangeMinRow, int? rangeMaxRow, int? rangeMinCol, int? rangeMaxCol,
            int pictureMinRow, int pictureMaxRow, int pictureMinCol, int pictureMaxCol, bool onlyInternal)
        {
            int _rangeMinRow = rangeMinRow ?? pictureMinRow;
            int _rangeMaxRow = rangeMaxRow ?? pictureMaxRow;
            int _rangeMinCol = rangeMinCol ?? pictureMinCol;
            int _rangeMaxCol = rangeMaxCol ?? pictureMaxCol;

            if (onlyInternal)
            {
                return (_rangeMinRow <= pictureMinRow && _rangeMaxRow >= pictureMaxRow &&
                        _rangeMinCol <= pictureMinCol && _rangeMaxCol >= pictureMaxCol);
            }
            return ((Math.Abs(_rangeMaxRow - _rangeMinRow) + Math.Abs(pictureMaxRow - pictureMinRow) >=
                     Math.Abs(_rangeMaxRow + _rangeMinRow - pictureMaxRow - pictureMinRow)) &&
                    (Math.Abs(_rangeMaxCol - _rangeMinCol) + Math.Abs(pictureMaxCol - pictureMinCol) >=
                     Math.Abs(_rangeMaxCol + _rangeMinCol - pictureMaxCol - pictureMinCol)));
        }

        #endregion



        public static int MergeTo(this ICell self, ICell target)
        {
            int firstRow = self.RowIndex;
            int lastRow = target.RowIndex;
            int firstCol = self.ColumnIndex;
            int lastCol = target.ColumnIndex;

            if (self.RowIndex > target.RowIndex)
            {
                firstRow = target.RowIndex;
                lastRow = self.RowIndex;
            }

            if (self.ColumnIndex > target.ColumnIndex)
            {
                firstCol = target.ColumnIndex;
                lastCol = self.ColumnIndex;
            }

            CellRangeAddress cellRangeAddress = new CellRangeAddress(firstRow, lastRow, firstCol, lastCol);
            return self.Sheet.AddMergedRegion(cellRangeAddress);
        }
    }
}
